package com.woniuxy01;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Scanner;

/**
 * @author 作者
 * @create 2025-09-23 14:14
 */
public class Test01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/db3";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("输入你要查询的学生的学号");
        int sid=scanner.nextInt();
        String sql="select * from stu where sid="+sid;
        Statement statement = connection.createStatement();
        //executeQuery()这个方法执行查询，返回值ResultSet,封装查询结果
        ResultSet resultSet = statement.executeQuery(sql);
        //next()方法，判断是否存在下一条数据，如果存在，获取下一条数据，每次获取一行数据(多列)
        if(resultSet.next()){
            //获取当前行行的某列数据,JDBC的下标从1开始
            System.out.println(resultSet.getInt(1));
            //也可以通过列名获取
            System.out.println(resultSet.getString("sname"));
            System.out.println(resultSet.getObject(3));
            System.out.println("--------------");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    private static void test04() throws ClassNotFoundException, SQLException {
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/db3";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql="select * from stu";
        Statement statement = connection.createStatement();
        //executeQuery()这个方法执行查询，返回值ResultSet,封装查询结果
        ResultSet resultSet = statement.executeQuery(sql);
        //next()方法，判断是否存在下一条数据，如果存在，获取下一条数据，每次获取一行数据(多列)
        while(resultSet.next()){
            //获取当前行行的某列数据,JDBC的下标从1开始
            System.out.println(resultSet.getInt(1));
            //也可以通过列名获取
            System.out.println(resultSet.getString("sname"));
            System.out.println(resultSet.getObject(3));
            System.out.println("--------------");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    private static void test03() throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/db3";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("输入要删除的学生的学号");
        int sid=scanner.nextInt();
        //"update stu set sname='"+sname++"',age="+age+" where sid="+sid;
        String sql="delete from stu where sid="+sid;
        Statement statement = connection.createStatement();
        int count = statement.executeUpdate(sql);
        System.out.println(count);
        statement.close();
        connection.close();
    }

    private static void test02() {
        Connection connection = null;
        Statement statement = null;
        try {
            Scanner scanner = new Scanner(System.in);
            String className="com.mysql.cj.jdbc.Driver";
            String url="jdbc:mysql://localhost:3306/db3";
            String user="root";
            String password="123";
            Class.forName(className);
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("输入学号");
            int sid=scanner.nextInt();
            System.out.println("输入姓名");
            String sname=scanner.next();
            System.out.println("输入年龄");
            int age=scanner.nextInt();
            String sql="insert into stu values("+sid+",'"+sname+"',"+age+")";
            statement = connection.createStatement();
            //执行sql,executeUpdate(sql),返回受影响的行数，执行增删改都使用这个方法
            int count = statement.executeUpdate(sql);
            System.out.println(count);
            statement.close();
            connection.close();
            System.out.println(sql);
        } catch (Exception e) {
//            throw new RuntimeException(e);
            e.printStackTrace();
        }finally {
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    private static void test01() throws ClassNotFoundException, SQLException {
        Scanner scanner = new Scanner(System.in);
        //JDBC四大核心参数
        String className="com.mysql.cj.jdbc.Driver";
        // http://www.woniux.com    https://www.baidu.com
        //连接规范，连接协议"jdbc:mysql://要连接的数据库所在服务器的IP地址:数据库启动占用的端口/要连接的数据库的名字"
        //String url="jdbc:mysql://localhost:3306/db3";
        //IP地址是网络中一台电脑的唯一标志
        //端口：一个端口是一个软件的唯一标志
        //如果连接本机的3306端口，可以省略
        String url="jdbc:mysql://localhost:3306/db3";
        String user="root";
        String password="123";
        //加载驱动:底层使用的反射加载JDBC核心驱动，就是创建了Driver类的对象
        //这句代码可以不写，只要项目中添加了jar包，系统会自动加载
        Class.forName(className);
        //创建连接对象
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("输入学号");
        int sid=scanner.nextInt();
        System.out.println("输入姓名");
        String sname=scanner.next();
        System.out.println("输入年龄");
        int age=scanner.nextInt();
        //定义sql语句
        String sql="insert into stu values("+sid+",'"+sname+"',"+age+")";
        //创建Statement对象:执行sql语句
        Statement statement = connection.createStatement();
        //执行sql,executeUpdate(sql),返回受影响的行数，执行增删改都使用这个方法
        int count = statement.executeUpdate(sql);
        System.out.println(count);
        //关闭资源
        statement.close();
        connection.close();
        System.out.println(sql);
    }
}
